package com.scholarship.modules.admin.service.impl;

import com.scholarship.common.utils.CommonUtil;
import com.scholarship.common.utils.ExcelUtil;
import com.scholarship.common.utils.NewFileUtil;
import com.scholarship.modules.admin.dto.CommonPrizeDto;
import com.scholarship.modules.admin.dto.ImportTemplateDto;
import com.scholarship.modules.admin.dto.StudentQueryDto;
import com.scholarship.modules.admin.entity.*;
import com.scholarship.modules.admin.exce.EmptyValueException;
import com.scholarship.modules.admin.exce.ImportFailException;
import com.scholarship.modules.admin.mapper.*;
import com.scholarship.modules.admin.service.InOutFileService;
import com.scholarship.modules.admin.service.RankingListService;
import com.scholarship.modules.admin.utils.RankingUtil;
import com.scholarship.modules.login.entity.Role;
import com.scholarship.modules.login.entity.User;
import com.scholarship.modules.login.mapper.LRoleMapper;
import com.scholarship.modules.login.mapper.LUserMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author Z.J.H
 * @create 2021/9/8 20:45
 */
@Slf4j
@Service
public class InOutFileServiceImpl implements InOutFileService {

    @Autowired
    private LUserMapper lUserMapper;

    @Autowired
    private LRoleMapper lRoleMapper;

    @Autowired
    private AUserInfoMapper aUserInfoMapper;

    @Autowired
    private ARewardApplicationMapper aRewardApplicationMapper;

    @Autowired
    private ARewardTypeDetailMapper aRewardTypeDetailMapper;

    @Autowired
    private ATableInfoMapper aTableInfoMapper;

    @Autowired
    private ACommonMapper aCommonMapper;

    @Autowired
    private ADropDownBoxMapper aDropDownBoxMapper;

    @Autowired
    private ARewardFeedbackMapper aRewardFeedbackMapper;

    @Autowired
    private MapSessionMapper mapSessionMapper;

    @Autowired
    private RankingListService rankingListService;

    @Autowired
    private NewFileUtil newFileUtil;

    @Autowired
    private ExcelUtil excelUtil;

    private static Pattern COMMON_PATTERN = Pattern.compile("^[a-z]([0-9]+)");

    /**
     * 下载指定导入模板
     * @param tableId
     * @param response
     * @throws Exception
     */
    @Override
    public void downLoadImportTemplate(Integer tableId, HttpServletResponse response) throws Exception {

        File file = createImportTemplateFile(tableId,true);

        //下载文件
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;" +
                "filename=" + URLEncoder.encode(file.getName(),"utf-8"));
        newFileUtil.downloadFile(file,response.getOutputStream());

        if(file.exists() && file.isFile()) {
            file.delete();
        }
    }

    /**
     * 下载导入模板压缩包
     * @param response
     * @throws Exception
     */
    @Override
    public void downLoadImportTemplateZip(HttpServletResponse response) throws Exception {

        List<File> files = new ArrayList<>();
        List<RewardTypeDetail> rewardTypeDetailList = aRewardTypeDetailMapper.listRewardTypeDetail();

        for(RewardTypeDetail rewardTypeDetail : rewardTypeDetailList) {
            files.add(createImportTemplateFile(rewardTypeDetail.getId(),false));
        }
        //四六级
        files.add(createImportTemplateFile(0,false));

        //下载压缩包
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;" +
                "filename=" + URLEncoder.encode("importTemplate.zip","utf-8"));
        newFileUtil.downloadZip(files,response.getOutputStream());

        for(File file : files) {
            if(file.exists() && file.isFile()) {
                file.delete();
            }
        }
    }

    /**
     * 下载学生信息表
     * @param studentQueryDto
     * @param response
     * @throws Exception
     */
    @Override
    public void downLoadStudentInfo(StudentQueryDto studentQueryDto, HttpServletResponse response) throws Exception {
        //下载前更新
        rankingListService.refreshRankingInformation();

        String[] heads = {"学号","姓名","专业","学年","行政班级","身份证","银行卡号","手机号","邮箱","综合测评总分","排名"};
        List<UserInfo> userInfoList = aUserInfoMapper.listExportStudentInfoByQuery(studentQueryDto);

        //生成Excel文件
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet();

        DataFormat format = workbook.createDataFormat();
        //创建单元格文本样式
        CellStyle textStyle = workbook.createCellStyle();
        textStyle.setDataFormat(format.getFormat("@"));
        //将部分列设为文本格式
        for(int i = 0;i < heads.length - 2;i++) {
            sheet.setDefaultColumnStyle(i, textStyle);
        }

        SXSSFRow row = sheet.createRow(0);
        SXSSFCell cell = null;
        //设置表头
        for(int i = 0;i < heads.length;i++) {
            cell = row.createCell(i);
            cell.setCellValue(heads[i]);
        }

        //插入数据
        int i = 1;
        for(UserInfo userInfo : userInfoList) {
            row = sheet.createRow(i);

            cell = row.createCell(0);
            cell.setCellValue(userInfo.getUserId());

            cell = row.createCell(1);
            cell.setCellValue(userInfo.getName());

            cell = row.createCell(2);
            cell.setCellValue(userInfo.getMajor());

            cell = row.createCell(3);
            cell.setCellValue(userInfo.getYear());

            cell = row.createCell(4);
            cell.setCellValue(userInfo.getStuClass());

            if(StringUtils.isNotBlank(userInfo.getIdCard())) {
                cell = row.createCell(5);
                cell.setCellValue(new String(Base64.getDecoder().decode(userInfo.getIdCard()), "utf-8"));
            }

            if(StringUtils.isNotBlank(userInfo.getBankCard())) {
                cell = row.createCell(6);
                cell.setCellValue(new String(Base64.getDecoder().decode(userInfo.getBankCard()), "utf-8"));
            }

            if(StringUtils.isNotBlank(userInfo.getPhone())) {
                cell = row.createCell(7);
                cell.setCellValue(new String(Base64.getDecoder().decode(userInfo.getPhone()), "utf-8"));
            }

            if(StringUtils.isNotBlank(userInfo.getEmail())) {
                cell = row.createCell(8);
                cell.setCellValue(new String(Base64.getDecoder().decode(userInfo.getEmail()), "utf-8"));
            }

            cell = row.createCell(9);
            cell.setCellValue(userInfo.getTotalScore());

            cell = row.createCell(10);
            cell.setCellValue(userInfo.getTotalRank());
            i++;
        }

        //下载文件
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;" +
                "filename=" + URLEncoder.encode("studentInfoTable.xlsx","utf-8"));
        workbook.write(response.getOutputStream());
    }

    /**
     * 导入通用模板
     * @param fileName
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importCommonTemplate(String fileName) throws Exception {
        //获取奖项简称
        Matcher matcher = COMMON_PATTERN.matcher(fileName);
        String tableName = null;
        if(matcher.find()) {
            tableName = matcher.group();
        }else {
            throw new ImportFailException("文件名格式有误,未包含奖项简称");
        }

        Integer tableId = aRewardTypeDetailMapper.getIdByTableName(tableName);
        if(tableId == null) {
            throw new ImportFailException(tableName + "该奖项简称不存在");
        }

        List<Row> rowList = new ArrayList<>();
        List<RewardApplication> rewardApplicationList = new ArrayList<>();

        //从第3行开始读取数据，包含表头字段
        excelUtil.setStartReadPos(2);
        try {
            rowList = excelUtil.readExcel(newFileUtil.getUploadPath() + fileName);
        }catch (IOException e) {
            log.error(e.getMessage(),e);
            //删除文件
            File file = new File(newFileUtil.getUploadPath() + fileName);
            if (file.isFile() && file.exists()) {
                file.delete();
            }
            throw new IOException("文件格式有误");
        }

        //获得表头字段
        Row headRow = rowList.get(0);
        //如果表头带序号，则说明该表不是模板并携带数据，以表格中的得分和状态为标准
        Boolean haveData = headRow.getCell(0).toString().equals("序号");
        Map<String,Integer> stateMap = null;
        if(haveData) {
            stateMap = mapSessionMapper.listStateNameIdMap();
        }

        //记录字段名及其下标
        HashMap<String,Integer> heads = new HashMap<>();
        for(int i = 0;i < headRow.getLastCellNum();i++) {
            heads.put(headRow.getCell(i).toString(), i);
        }
        rowList.remove(0);

        //获取字段属性名集合、中文名集合和分值字段名
        List<String> fields = new ArrayList<>();
        List<String> chineseNames = new ArrayList<>();
        String scoreName = null;
        fields.add("user_id");
        chineseNames.add("学号");
        List<TableInfo> tableInfoList = aTableInfoMapper.listTableInfoByTableId(tableId);
        for(TableInfo tableInfo : tableInfoList) {
            fields.add(tableInfo.getAttributeName());
            chineseNames.add(tableInfo.getItemName());
            //如果该字段是分值
            if(tableInfo.getFieldId() == 4) {
                scoreName = tableInfo.getItemName();
            }
        }

        //获取导入年份
        Calendar calendar = Calendar.getInstance();
        String importYear = String.valueOf(calendar.get(Calendar.YEAR));

        //获取奖项对应的选项得分对
        Map<String,Double> boxs = mapSessionMapper.listOptionScoreMapBytableId(tableId);

        //遍历导入数据
        List<List<String>> listList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        for(Row row : rowList) {
            //预先判断是否存在该学生的账号
            String userId = row.getCell(heads.get("学号")).getStringCellValue();
            if (aUserInfoMapper.getCountByUserId(userId) == 0) {
                sb.append(userId + ",");
                continue;
            }

            List<String> list = new ArrayList<>();
            for(String head : chineseNames) {
                XSSFCell cell = (XSSFCell) row.getCell(heads.get(head));
                String value = null;
                //如果位置不为空，则进行类型判断
                if(cell != null) {
                    switch(cell.getCellType()) {
                        case NUMERIC:
                            //匹配时间类型
                            if("yyyy\\-m\\-d".equals(cell.getCellStyle().getDataFormatString())){
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                value = sdf.format(cell.getDateCellValue());
                            }else {
                                cell.setCellType(CellType.STRING);
                                value = cell.getStringCellValue();
                            }
                            break;
                        default:
                            cell.setCellType(CellType.STRING);
                            value = cell.getStringCellValue();
                    }
                }
                list.add(value);
            }
            listList.add(list);


            RewardApplication rewardApplication = new RewardApplication();
            rewardApplication.setUserId(userId);
            rewardApplication.setTableId(tableId);
            rewardApplication.setImportYear(importYear);
            if(haveData) {
                rewardApplication.setScore(row.getCell(heads.get("得分")).getNumericCellValue());
                rewardApplication.setState(stateMap.get(row.getCell(heads.get("状态")).getStringCellValue()));
                if(row.getCell(heads.get("不通过理由")) != null) {
                    rewardApplication.setFeedback(row.getCell(heads.get("不通过理由")).getStringCellValue());
                }
            }else {
                //表中不携带数据，分数以下拉框查询得分为标准，state默认为通过1
                rewardApplication.setScore(boxs.get(row.getCell(heads.get(scoreName)).getStringCellValue()));
                rewardApplication.setState(1);
            }

            rewardApplicationList.add(rewardApplication);
        }
        System.out.println("以下学生账号不存在：" + sb);

        if (!rewardApplicationList.isEmpty()) {
            CommonPrizeDto commonPrizeDto = new CommonPrizeDto(tableName, fields, listList);

            aCommonMapper.insertAndUpdateSelectedRewardList(commonPrizeDto);
            aRewardApplicationMapper.insertAndUpdateRewardApplicationList(rewardApplicationList);
        }

        File file = new File(newFileUtil.getUploadPath() + fileName);
        if(file.exists() && file.isFile()) {
            file.delete();
        }
    }

    /**
     * 导入学生信息模板
     * @param fileName
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importStudentTemplate(String fileName) throws Exception {

        List<Row> rowList = new ArrayList<>();
        List<User> userList = new ArrayList<>();
        List<Role> roleList = new ArrayList<>();
        List<UserInfo> userInfoList = new ArrayList<>();
        List<RewardApplication> rewardApplicationList = new ArrayList<>();

        //从第3行开始读取数据，包含表头字段
        excelUtil.setStartReadPos(2);
        try {
            rowList = excelUtil.readExcel(newFileUtil.getUploadPath() + fileName);
        }catch (IOException e) {
            log.error(e.getMessage(),e);
            //删除文件
            File file = new File(newFileUtil.getUploadPath() + fileName);
            if (file.isFile() && file.exists()) {
                file.delete();
            }
            throw new IOException("文件格式有误");
        }

        //获得表头字段
        Row headRow = rowList.get(0);
        //记录字段名及其下标
        HashMap<String,Integer> heads = new HashMap<>();
        for(int i = 0;i < headRow.getLastCellNum();i++) {
            heads.put(headRow.getCell(i).toString(), i);
        }
        rowList.remove(0);

        //获取学年(20xx-20xx)和专业(18计科)
        String year = fileName.substring(0,9);
        String major = fileName.substring(fileName.indexOf("(") + 1,fileName.indexOf(")"));

        //获取导入年份
        Calendar calendar = Calendar.getInstance();
        String importYear = String.valueOf(calendar.get(Calendar.YEAR));

        //遍历导入数据
        for(Row row : rowList) {
            String userId = row.getCell(heads.get("学号")).getStringCellValue();
            String name = row.getCell(heads.get("姓名")).getStringCellValue();
            Double score = row.getCell(heads.get("加权平均分")).getNumericCellValue();
            //将学号作为初始密码进行MD5盐值加密
            String password = CommonUtil.encryptPassword(userId,userId);

            //账号信息(状态：1正常)
            User user = new User(null, userId, name, password, 1);
            userList.add(user);

            //身份信息(身份:1学生)
            Role role = new Role(null, userId, 1);
            roleList.add(role);

            //基本学生信息
            UserInfo userInfo = new UserInfo();
            userInfo.setUserId(userId);
            userInfo.setName(name);
            userInfo.setImportYear(importYear);
            userInfo.setYear(year);
            userInfo.setMajor(major);
            userInfo.setStuClass(row.getCell(heads.get("班级")).getStringCellValue());
            userInfo.setScore(score);
            userInfo.setFailNum((int)row.getCell(heads.get("不及格门数")).getNumericCellValue());
            userInfo.setTotalScore(.0);
            userInfo.setTotalRank(0);
            userInfo.setCet4(0);
            userInfo.setCet6(0);
            userInfoList.add(userInfo);

            //加权平均分(tableId为1)奖项得分信息，状态默认为已通过
            RewardApplication rewardApplication = new RewardApplication();
            rewardApplication.setUserId(userId);
            rewardApplication.setTableId(1);
            rewardApplication.setImportYear(importYear);
            rewardApplication.setScore(score);
            rewardApplication.setState(1);
            rewardApplicationList.add(rewardApplication);
        }

        //根据加权平均分排名
        userInfoList = RankingUtil.rankingScoreInUserInfo(userInfoList,false);

        //已存在的更新状态
        lUserMapper.insertAndUpdateUserList(userList);
        lRoleMapper.insertRoleList(roleList);
        aUserInfoMapper.insertAndUpdateUserInfoList(userInfoList);
        aRewardApplicationMapper.insertAndUpdateRewardApplicationList(rewardApplicationList);

        File file = new File(newFileUtil.getUploadPath() + fileName);
        if(file.exists() && file.isFile()) {
            file.delete();
        }
    }

    /**
     * 导入四六级模板
     * @param fileName
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importCetTemplate(String fileName) throws Exception {

        List<Row> rowList = new ArrayList<>();
        List<UserInfo> userInfoList = new ArrayList<>();
        List<RewardApplication> rewardApplicationList = new ArrayList<>();
        List<List<String>> listList = new ArrayList<>();

        //从第3行开始读取数据，包含表头字段
        excelUtil.setStartReadPos(2);
        try {
            rowList = excelUtil.readExcel(newFileUtil.getUploadPath() + fileName);
        }catch (IOException e) {
            log.error(e.getMessage(),e);
            //删除文件
            File file = new File(newFileUtil.getUploadPath() + fileName);
            if (file.isFile() && file.exists()) {
                file.delete();
            }
            throw new IOException("文件格式有误");
        }

        //获得表头字段
        Row headRow = rowList.get(0);
        //记录字段名及其下标
        HashMap<String,Integer> heads = new HashMap<>();
        for(int i = 0;i < headRow.getLastCellNum();i++) {
            heads.put(headRow.getCell(i).toString(), i);
        }
        rowList.remove(0);

        //获取导入年份
        Calendar calendar = Calendar.getInstance();
        String importYear = String.valueOf(calendar.get(Calendar.YEAR));

        //外语成绩表字段
        List<String> fields = new ArrayList<>();
        fields.add("user_id");
        fields.add("exam_name");
        fields.add("exam_score");

        //遍历导入数据
        StringBuilder sb = new StringBuilder();
        for(Row row : rowList) {
            //预先判断学生账号是否存在
            String userId = row.getCell(heads.get("学号")).getStringCellValue();
            if (aUserInfoMapper.getCountByUserId(userId) == 0) {
                sb.append(userId + ",");
                continue;
            }
            Integer cet4 = (int)row.getCell(heads.get("CET4")).getNumericCellValue();
            Integer cet6 = (int)row.getCell(heads.get("CET6")).getNumericCellValue();
            Double score = cet6 >= 425 ? 4.0 : (cet4 >= 425 ? 2.0 : .0);

            UserInfo userInfo = new UserInfo();
            userInfo.setUserId(userId);
            userInfo.setCet4(cet4);
            userInfo.setCet6(cet6);
            userInfoList.add(userInfo);

            //CET46成绩加分等同于外语成绩加分（tableId=2），状态默认通过
            RewardApplication rewardApplication = new RewardApplication();
            rewardApplication.setUserId(userId);
            rewardApplication.setTableId(2);
            rewardApplication.setImportYear(importYear);
            rewardApplication.setScore(score);
            rewardApplication.setState(1);
            rewardApplicationList.add(rewardApplication);

            List<String> list = new ArrayList<>();
            list.add(userId);
            list.add(score.equals(4.0) ? "CET6" : "CET4");
            list.add(score.equals(4.0) ? cet6.toString() : cet4.toString());
            listList.add(list);
        }
        System.out.println("以下学生账号不存在：" + sb);

        if (!rewardApplicationList.isEmpty()) {
            aUserInfoMapper.updateCetList(userInfoList);
            aRewardApplicationMapper.insertAndUpdateRewardApplicationList(rewardApplicationList);
            aCommonMapper.insertAndUpdateSelectedRewardList(new CommonPrizeDto("a2", fields, listList));
        }

        File file = new File(newFileUtil.getUploadPath() + fileName);
        if(file.exists() && file.isFile()) {
            file.delete();
        }
    }

    /**
     * 创建导入模板文件
     * @param tableId
     * @param haveData 是否携带数据
     * @return
     * @throws Exception
     */
    @Override
    public File createImportTemplateFile(Integer tableId, Boolean haveData) throws Exception {

        RewardTypeDetail rewardTypeDetail = aRewardTypeDetailMapper.getRewardTypeDetailByTableId(tableId);
        if(tableId != 0 && rewardTypeDetail == null) {
            throw new EmptyValueException("该tableId不存在对应表格");
        }

        ImportTemplateDto importTemplateDto = new ImportTemplateDto();
        if(tableId == 1) {
            importTemplateDto = new ImportTemplateDto(
                    "20××-20××学年学生成绩汇总表(××计科).xlsx",
                    "成绩导入模版  文件名格式：20××-20××学年学生成绩汇总表(××计科)  专业只能为两个字，计科2+2为计2",
                    "学号、姓名、班级、加权平均分、不及格门数 均为必填项",
                    Arrays.asList(new String[] {"学号","姓名","班级","加权平均分","不及格门数"})
            );
        }else if(tableId == 0) {
            importTemplateDto = new ImportTemplateDto(
                    "四六级导入模板.xlsx",
                    "四六级导入模板",
                    "学号、CET4、CET6 均为必填项",
                    Arrays.asList(new String[] {"学号","姓名","班级","CET4","CET6"})
            );
        }else {
            List<String> head = new ArrayList<>(Arrays.asList(new String[]{"学号","姓名","班级"}));
            String originakFileName = rewardTypeDetail.getTableName() + rewardTypeDetail.getName() + (haveData ? ".xlsx" : "模板.xlsx");
            List<String> heads = new ArrayList<>();
            heads.addAll(head);
            heads.addAll(aTableInfoMapper.listChineseNameByTableId(tableId));

            importTemplateDto.setOriginalFileName(originakFileName);
            importTemplateDto.setHeads(heads);
            importTemplateDto.setFristRow(rewardTypeDetail.getDetail());
            importTemplateDto.setSecondRow("除了姓名、班级，其余均为必填项");
        }

        //对应表字段信息列表
        List<TableInfo> tableInfoList = null;
        if(tableId != 1 && tableId != 0) {
            tableInfoList = aTableInfoMapper.listTableInfoByTableId(tableId);
        }

        //文件名
        String originalFileName = importTemplateDto.getOriginalFileName();
        //表头字段
        List<String> heads = importTemplateDto.getHeads();
        //表头字段对应数据库表字段名
        List<String> fieldNames = new ArrayList<>();
        if(haveData) {
            heads.add(0,"序号");
            heads.add("得分");
            heads.add("状态");
            heads.add("不通过理由");
            heads.add("附属文件名");
            fieldNames.addAll(Arrays.asList(new String[]{"user_id","NAME","stu_class","score","state_name","feedback","file_path"}));
            fieldNames.addAll(3, aTableInfoMapper.listFieldByTableId(tableId));
        }

        //生成Excel文件
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet();

        DataFormat format = workbook.createDataFormat();
        //创建单元格文本样式
        CellStyle textStyle = workbook.createCellStyle();
        textStyle.setDataFormat(format.getFormat("@"));

        //创建单元格日期样式
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(format.getFormat("yyyy-m-d"));

        //创建单元格小数点后2位样式
        CellStyle decimalStyle = workbook.createCellStyle();
        decimalStyle.setDataFormat(format.getFormat("#,##0.00"));

        //各列添加样式
        //学号/序号默认为文本
        sheet.setDefaultColumnStyle(0,textStyle);
        sheet.setDefaultColumnStyle(1,textStyle);
        //动态表头添加样式
        if (tableInfoList != null) {
            for(int i = 0;i < tableInfoList.size();i++) {
                Integer fieldId = tableInfoList.get(i).getFieldId();
                //是否携带数据（序号列是否存在）
                Integer column = i + (haveData ? 4 : 3);
                //日期
                if(fieldId == 2) {
                    sheet.setDefaultColumnStyle(column, dateStyle);
                }
                //下拉框和分值下拉框
                else if(fieldId == 3 || fieldId == 4) {
                    List<String> boxs = aDropDownBoxMapper.listDropDownBoxNameByFieldId(tableId);
                    setDropDownBoxStyle(sheet,3,5000, column, column,boxs.toArray(new String[boxs.size()]));
                }
            }
        }
        //状态添加下拉框
        if (haveData) {
            List<String> states = aRewardFeedbackMapper.listState();
            setDropDownBoxStyle(sheet,3,5000, heads.size() - 3, heads.size() - 3,states.toArray(new String[states.size()]));
        }

        //设置一二行备注
        SXSSFRow row = sheet.createRow(0);
        SXSSFCell cell = row.createCell(0);
        cell.setCellValue(importTemplateDto.getFristRow());
        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(importTemplateDto.getSecondRow());

        //设置表头
        row = sheet.createRow(2);
        for(int i = 0;i <heads.size();i++) {
            cell = row.createCell(i);
            cell.setCellValue(heads.get(i));
        }

        //添加学生奖项数据
        if(haveData) {
            List<String> fields = aTableInfoMapper.listFieldByTableId(tableId);
            List<Map<String,Object>> excelInfos = aCommonMapper.listStudentRewardExcel(tableId,rewardTypeDetail.getTableName(),fields);

            for(int i = 0;i < excelInfos.size();i++) {
                //跳过两行备注
                row = sheet.createRow(i + 3);
                Map<String, Object> cellMap = excelInfos.get(i);

                //序号
                cell = row.createCell(0);
                cell.setCellValue(i + 1);

                //其他表头
                for(int j = 0;j < fieldNames.size();j++) {
                    if(cellMap.get(fieldNames.get(j)) == null) {
                        continue;
                    }
                    //重写附属文件名
                    if(j == heads.size() - 2) {
                        String[] tmp = cellMap.get("file_path").toString().split("\\.");
                        cellMap.put("file_path",cellMap.get("user_id") + "_" + cellMap.get("NAME") + "." + tmp[tmp.length - 1]);
                    }

                    cell = row.createCell(j + 1);
                    //得分做小数点限制
                    if(j == heads.size() - 5) {
                        cell.setCellStyle(decimalStyle);
                        cell.setCellValue(Double.parseDouble(cellMap.get(fieldNames.get(j)).toString()));
                    }else {
                        cell.setCellValue(cellMap.get(fieldNames.get(j)).toString());
                    }
                }
            }
        }

        //保存表格
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(newFileUtil.getDownloadPath() + originalFileName);
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return new File(newFileUtil.getDownloadPath() + originalFileName);
    }

    /**
     * 设置下拉框样式
     * @param sheet
     * @param firstRow  起始行
     * @param lastRow   终止行
     * @param firstCol  起始列
     * @param lastCol   终止列
     * @param boxInfo   下拉框列表
     */
    private void setDropDownBoxStyle(SXSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String[] boxInfo) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        //构造constraint对象
        DataValidationConstraint constraint = helper.createExplicitListConstraint(boxInfo);
        //数据有效性对象
        DataValidation validation = helper.createValidation(constraint, regions);
        //处理Excel兼容性问题
        if (validation instanceof XSSFDataValidation) {
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        } else {
            validation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(validation);
    }
}
